"""

.. module:: database

*Description*:

This module contains all DataStore related classes and definitions. The 
DataStore provides persistent storage of Resources that include data and code.

While there are no restrictions in the name of a DataStore, a UUID type 4 
(random) and the extension '.datastore' is recommended.

In the node environment, a DataStore is created during node initialisation and
contains everything relating to that node.

A DataStore name of ':memory:' is permitted, if an in-memory DataStore is
required.

*Constants*:

.. data:: DEVEL
        
    DEVEL is a logging level between logging.DEBUG and logging.INFO.
        
.. data:: TRACE
        
    TRACE is the lowest logging level. All log messages are dumped.

.. seealso:: class :class:`datastore.resource.Resource`, 
    :class:`datastore.uri.Uri`
"""

# Imports
#
import logging
import os
import sqlite3
import types
import zlib

from datastore.resource import Resource
from datastore.uri import Uri
import datetime

# Constants
#
DEVEL = 15
TRACE = 5

# Classes
#
class DataStore(object):
    """
    The purpose of the DataStore class is to manage the DataStore - a database 
    is a repository for Resource objects. Resources are serialised and 
    compressed before being written to the DataStore, and decompressed and 
    de-serialised after being read from the DataStore.
    
    Note: Commits are done after all functions to prevent::
    
        sqlite3.OperationalError: cannot commit transaction - SQL statements 
        in progress
    
    Normally, a datastore filename is specified and the datastore is opened.
    
    In the node environment, each thread has its own connection. This is
    highly recommended in other environments. because of these multiple
    connections, lock() and unlock() are not used. It is assumed that some
    other mechanism, such as binding to a port, will be used to control
    access to the database file.
    
    A 'conn' may be supplied in the special case of node initialisation,
    where this class has not yet been loaded. It is assumed that a 
    row_factory has already been setup.
    
    The supplied connection must set an additional attribute in the constructor,
    'database_filename'. This allows a new connection to the same database to 
    be easily setup::
    
        self.datastore = DataStore(self.datastore.filename)
    
    In-memory datastores are supported. Use ':memory:' (without the quotes) as 
    the filename.

    **Constants**:
    
    :param filename: the filename of the datastore.
    :param conn: If the datastore has already been opened, then this can
            be used.
    """
    
    def __init__(self, filename=None, conn=None):        
        #locked is initially none
        self.locked = None
        
        self.logger = logging.getLogger('yarra.datastore.database.DataStore')
        if conn is not None:
            self.conn = conn
        else:
            if (not os.path.isfile(filename)) or (filename == ':memory:'):
                self.conn = sqlite3.connect(filename, check_same_thread=False)
                self._create_database()
            else:
                self.conn = sqlite3.connect(filename, check_same_thread=False)
            self.conn.row_factory = sqlite3.Row

        self.filename = filename
        self.logger.log(logging.INFO, "New database connection for (%s)", 
                                                    filename)

    def lock(self):
        """
        This function locks the datastore from other processes. The only way 
        we can lock the SQLite3 database is by performing the following actions:

        1. SQL query "PRAGMA locking_mode=EXCLUSIVE"
        2. SQL INSERT or UPDATE.
        3. Perform all SQL on the same connection, as the file lock is attached
           to the connection. If the connection is closed then the lock is 
           released (### this doesn't seem to work###).
        
        The database will lock only on the first write after the PRAGMA call. 
        """
        
        cursor = self.conn.cursor()
        cursor.execute("""PRAGMA locking_mode=EXCLUSIVE""")
        cursor.close()
        self.conn.commit()
        
        type_uri = Uri('internal:base/datastore/database/nodelock')
        res_list = None
        
        try:
            res_list = self.find_type(type_uri)
        except NotFoundException:
            pass
        
        if (res_list is not None) and (len(res_list) > 0):
            res = res_list[0]
        else:
            res = Resource(type=type_uri)
            res.dc_title('DataStore Lock (exclusive)')
            res.dc_description("This resource exists to force "
            "'PRAGMA locking_mode=EXCLUSIVE' in datastore.lock().")

        self.put([res])
        self.locked = True
    
    def unlock(self):
        """
        This function unlocks the datastore from other processes. The only way 
        we can unlock the database is by performing the following actions:
        
        1. SQL query "PRAGMA locking_mode=NORMAL"
        2. SQL INSERT or UPDATE.
        3. Perform all SQL on the same connection, as the file lock is attached
           to the connection. If the connection is closed then the lock is 
           released (### this doesn't seem to work###).
        
        The database will lock only on the first write after the PRAGMA call. 
        """
        
        if self.locked:
            cursor = self.conn.cursor()
            cursor.execute("""PRAGMA locking_mode=NORMAL""")
            cursor.close()
            self.conn.commit()
            
            type_uri = Uri('internal:base/datastore/database/nodelock')
            res_list = None
            
            try:
                res_list = self.find_type(type_uri)
            except NotFoundException:
                pass
            
            if (res_list is not None) and (len(res_list) > 0):
                res = res_list[0]
            else:
                res = Resource(type=type_uri)
                res.dc_title('DataStore Lock (normal)')
                res.dc_description("This resource exists to force "
                "'PRAGMA locking_mode=NORMAL' in datastore.lock().")

            self.put([res])

    def get(self, ids):
        """
        This function returns a list of Resources that each have an id that
        exists in the argument 'ids' list.
        
        Each id is fetched with its own SQL statement so performance might be 
        an issue if a large number of id's are specified.
        
        Non-string id's are converted to string.
        
        Example::
        
            refreshed_res = ds.get(old_res.get_id())
        
        :param ids: int or List of int's - Resource id(s). An id may also be a 
            string or unicode type.
        :rtype: List - list of Resources.
        """
        
        # allow a single resource to be fetched
        if (type(ids) == types.IntType) or \
           (type(ids) == types.StringType) or \
           (type(ids) == types.UnicodeType):
            ids = [ids]
        
        self.logger.log(TRACE, "ids (%s)", ids)
        
        cursor = self.conn.cursor()
        
        # TODO: could not get the IN clause working, so for now it is running 
        # a separate query for each id
        
        # find each resource by id
        resources = []
        for id in ids:
            id = str(id)

            cursor.execute("""
                            SELECT r.id as RESOURCE_ID, 
                                   r.object as RESOURCE_OBJECT
                            FROM RESOURCE r
                            WHERE r.id = (?)
                            """, [id])
            
            # only one row should return
            resource_row = cursor.fetchone()
            cursor.close()
            #self.conn.commit()
            if resource_row is None:
                raise NotFoundException()
            else:
                #res = Resource(serialised=resource_row['RESOURCE_OBJECT'])
                res = Resource(repr=zlib.decompress(
                                        resource_row['RESOURCE_OBJECT']))
                res.set_id(resource_row['RESOURCE_ID'])               
                resources.append(res)
                    
        self.logger.log(TRACE, "resources (%s)", resources)
        return resources
        
    def put(self, resources, commit=True):
        """
        This function either saves a new Resource or updates a existing one. 
        The argument 'resources' is a resource or a list of Resources. This
        method does not return anything.
        
        'commit=False' allows a larger atomic transaction to be created by
        combining a number of put and delete functions together. The
        delete_put() method makes use of this parameter. Note, the last 
        method to be called must omit this parameter or specify 'commit=True'.
        
        Resources are zip compressed before being stored.
        
        Each Resource is added in its own SQL statement. This is not a 
        high performance technique. 
        
        FIXME: Each resource is saved twice, once to create the row, and the 
        second time to add the id to the Resource. Could be improved by setting
        the id in the get() method. find() already sets the id.
        
        Example::
        
            ds = DataStore(filename=':memory:')
            res = Resource(type='term:base/datastore/resource/test')
            res.hello = 'world!'
            ds.put(res)

        :param resources: Resource or List of Resources
        :param commit: do a database commit after the Resource is saved.
        """
        
        # allow a single resource to be specified
        if type(resources) == type(Resource(empty=True)):
            resources = [resources]
        
        cursor = self.conn.cursor()
        
        for res in resources:
            # If Resource does not have an 'id' then it hasn't yet been saved
            # to the database, where the 'id' is assigned. So let's INSERT it.
            if res.get_id() is None:
                cursor.execute("""
                                INSERT INTO RESOURCE VALUES (null, ?)
                                """,
                                #[res.serialise()])
                                [sqlite3.Binary(zlib.compress(repr(res)))
                                ])
                
                # set the id of the resource (as a string)
                res.set_id(str(cursor.lastrowid))
            
            # If Resource does have an 'id' then it has been saved (at some
            # time in the past) to the database. So let's UPDATE it.
            else:
                cursor.execute("""
                                UPDATE RESOURCE
                                SET object = (?)
                                WHERE id = (?)
                                """,
                                #[res.serialise(), res.get_id()])
                                [sqlite3.Binary(zlib.compress(repr(res))), 
                                 res.get_id()])
                
                # Delete all Metadata records for this Resource. We will create
                # new records at the next step.
                cursor.execute("""
                                DELETE FROM RESOURCEMETADATA
                                WHERE resource_id = (?)""",
                                [res.get_id()])
                
            # insert the resource metadata
            # the id is stored as a string in Resource so must be converted back
            # to int.
            for key in res.get_metadata_keys():
                value_list = res.get_metadata(key)
                if type(value_list) != types.ListType:
                    value_list = [value_list]
                for value in sorted(value_list):
                    (key_str, value_str) = self.convert_key_value_pair_to_str(
                                                (key, value))
                    cursor.execute("""
                                INSERT INTO RESOURCEMETADATA VALUES (?, ?, ?)
                                """, (int(res.get_id()), key_str, value_str))             
        cursor.close()
        if commit:
            self.conn.commit()
    
    def delete(self, resources, commit=True):
        """
        This function deletes the argument 'resources' Resources from the 
        DataStore.
        
        Each Resource is deleted in its own SQL statement. This is not a 
        high performance technique.
        
        Stored procedures are used to delete the metadata values.
        
        Example::
        
            resources = ds.find_type(uri('term:base/datastore/resource/test'))
            ds.delete(resources)
            
        You cannot put a resource and then delete it because put doesn't 
        update the id in the resource. You must get it or find it in the
        DataStore, so that the resource correctly has its id set.
        
        :param resources: Resource or list of Resources.
        :param commit: True to do the commit here, False if done elsewhere.
        """
        
        # allow a single resource to be specified
        if type(resources) == type(Resource(empty=True)):
            resources = [resources]
        
        cursor = self.conn.cursor()

        # TODO: use single "DELETE ... WHERE id IN ..." clause rather than 
        # separate "DELETE ... WHERE id = " clauses?
        
        for res in resources:
            # check the type first
            if not isinstance(res, Resource):
                raise TypeError
            
            # delete the resource. There is a trigger that will delete anything 
            # that also references this resource (i.e metadata)
            cursor.execute("""
                            DELETE FROM RESOURCE
                            WHERE id = (?)
                            """,
                            [res.get_id()])
        
        cursor.close()
        if commit:
            self.conn.commit()
    
    def find(self, key_values=None):
        """
        This function returns a list of Resources that have metadata that
        match ALL those defined in the argument 'key_values'.
        
        If value is None, then all resources with the specified metadata key
        are returned. If both key and value are None, then all resources in the
        datastore are returned.
        
        Example::
        
            all_resources = ds.find([(None, None)])
            
            uri = Uri('term:base/datastore/resource/test')
            test = ds.find([(Uri('term:base/datastore/resource/type'), uri)])
        
        :param key_values: List of tuple(key, value)
        :rtype: List - list of Resources.
        """
        
        cursor = self.conn.cursor()
        resource_results = None
        for key_value in key_values:
            (key_str, value_str) = self.convert_key_value_pair_to_str(key_value)
            self.logger.log(TRACE, "key (%s), value (%s)", key_str, value_str)
            
            if value_str is None:
                if key_str is None:
                    cursor.execute("""
                                SELECT RESOURCE.ID AS RESOURCE_ID, 
                                       RESOURCE.OBJECT AS RESOURCE_OBJECT
                                FROM RESOURCE
                                JOIN RESOURCEMETADATA
                                ON RESOURCE.ID = RESOURCEMETADATA.RESOURCE_ID
                               """)
                else:
                    cursor.execute("""
                                SELECT RESOURCE.ID AS RESOURCE_ID, 
                                       RESOURCE.OBJECT AS RESOURCE_OBJECT
                                FROM RESOURCE
                                JOIN RESOURCEMETADATA
                                ON RESOURCE.ID = RESOURCEMETADATA.RESOURCE_ID
                                WHERE RESOURCEMETADATA.KEY = (?)
                                """, [key_str])
            else:
                cursor.execute("""
                                SELECT RESOURCE.ID AS RESOURCE_ID, 
                                       RESOURCE.OBJECT AS RESOURCE_OBJECT
                                FROM RESOURCE
                                JOIN RESOURCEMETADATA
                                ON RESOURCE.ID = RESOURCEMETADATA.RESOURCE_ID
                                WHERE RESOURCEMETADATA.KEY = (?)
                                AND RESOURCEMETADATA.VALUE = (?)
                                """, [key_str, value_str])
                
            rows = cursor.fetchall()
            cursor.close()
            #self.conn.commit()
            if len(rows) == 0:
                raise NotFoundException()
            else:
                if resource_results is None:
                    resource_results = {}
                    for row in rows:
                        id = row['RESOURCE_ID']
                        #res = Resource(row['RESOURCE_OBJECT'])
                        res = Resource(
                                repr=zlib.decompress(row['RESOURCE_OBJECT']))
                        res.set_id(id)
                        resource_results[id] = res
                else:
                    new_results = {}
                    for row in rows:
                        id = row['RESOURCE_ID']
                        if id in resource_results:
                            new_results[id] = resource_results[id]
                    resource_results = new_results                  
        return resource_results.values()

    def find_type(self, uri=None):
        """
        This function returns a list of Resources that have a type that
        matches the specified uri. This is a convenience method that invokes
        find() with the resource type Uri.
        
        Example::
            
            res_list = ds.find_type(Uri('term:base/datastore/resource/test'))
        
        :param uri: type uri
        :rtype: List - list of matching Resources.
        """
        if type(uri) != type(Uri()):
            raise TypeError("Key (%s) must be a Uri." % str(uri))
        return self.find([(Uri('term:base/datastore/resource/type'), uri)])

    def delete_put(self, delete_resources, put_resources):
        """
        This function deletes the argument 'delete_resources' Resources from the
        DataStore, and adds the argument 'put_resources' Resources to the 
        DataStore. 
        
        All the actions are performed in a single atomic transaction (that is,
        as part of a single connection).
        
        Example::
        
            ds = DataStore(filename=':memory:')
            new_res = Resource(type='term:base/datastore/resource/test')
            new_res.hello = 'world!'
            old_res = ds.find_type(Uri('term:base/datastore/resource/test'))
            ds.delete_put(old_res, new_res)

        :param delete_resources: Resource or list of Resources to delete from 
                DataStore.
        :param put_resources: Resource or list of Resources to add to DataStore.
        """
        
        self.delete(delete_resources, commit=False)
        self.put(put_resources, commit=False)
        self.conn.commit()
    
    def get_log(self, ids=None):
        """
        This function returns a list of Strings that each have an id that
        exists in the argument 'ids' list.
        
        Each id is fetched with its own SQL statement so performance might be 
        an issue if a large number of id's are specified.
        
        If no 'id' argument is passed then all log messages are returned.
        
        Non-string id's are converted to string.
        
        Example::
        
            refreshed_res = ds.get_log([ids])
        
        :param ids: int or List of int's - Message id(s). An id may also be a 
            string or unicode type.
        :rtype: List - list of tuples (message, date_created).
        """
        
        # allow a single resource to be fetched
        if (ids is not None):
            if (type(ids) == types.IntType) or \
               (type(ids) == types.StringType) or \
               (type(ids) == types.UnicodeType):
                ids = [ids]
        
        self.logger.log(TRACE, "ids (%s)", ids)
        
        cursor = self.conn.cursor()
        
        message_list = []
        if (ids is not None):
            for id in ids:
                id = str(id)
    
                cursor.execute("""
                                SELECT VALUE AS MESSAGE, DATE_CREATED
                                FROM LOG l
                                WHERE l.id = (?)
                                """, 
                                [id])
                
                # only one row should return
                log_row = cursor.fetchone()
                cursor.close()
                #self.conn.commit()
                if (log_row is not None):
                    message_list.append((log_row['MESSAGE'], log_row['DATE_CREATED']))
        else:
            cursor.execute("""
                            SELECT VALUE AS MESSAGE, DATE_CREATED
                            FROM LOG l
                            """, 
                            [])
            log_rows = cursor.fetchall()
            cursor.close()
            #self.conn.commit()
            if (len(log_rows) > 0):
                for log_row in log_rows:
                    message_list.append((log_row['MESSAGE'], log_row['DATE_CREATED']))
        
        self.logger.log(TRACE, "message_list (%s)", message_list)
        return message_list
    
    def get_n_logs(self, n=50, desc=True):
        """
        This function returns a list of tuples that represent a log entry.
        It allows you to specify the number of log entries you want to 
        retrieve and the order.

        :param n: The number of log entries you want to retrieve
        :param desc: Order of results. 
        :rtype: List - list of tuples (message, date_created).
        """
        
        cursor = self.conn.cursor()
        
        message_list = []
        if desc:
            cursor.execute("""
                            SELECT VALUE AS MESSAGE, DATE_CREATED
                            FROM LOG l
                            ORDER BY ID DESC
                            LIMIT (?)
                            """, 
                            [n])
        else:
            cursor.execute("""
                            SELECT VALUE AS MESSAGE, DATE_CREATED
                            FROM LOG l
                            ORDER BY ID ASC
                            LIMIT (?)
                            """, 
                            [n])
        log_rows = cursor.fetchall()
        cursor.close()
        #self.conn.commit()
        if (len(log_rows) > 0):
            for log_row in log_rows:
                message_list.append((log_row['MESSAGE'], log_row['DATE_CREATED']))
        
        self.logger.log(TRACE, "message_list (%s)", message_list)
        return message_list            
                
    def log(self, message, commit=True):
        """
        This function saves a new log message. 
        
        'commit=False' allows a larger atomic transaction to be created by
        combining a number of put and delete functions together. The
        delete_put() method makes use of this parameter. Note, the last 
        method to be called must omit this parameter or specify 'commit=True'.
        
        Example::
        
            ds = DataStore(filename=':memory:')
            msg = "I'm a log message"
            ds.log(msg)

        :param message: String
        :param commit: do a database commit after the Resource is saved.
        :rtype: Integer - the message id.
        """
        
        cursor = self.conn.cursor()
        cursor.execute("""
                        INSERT INTO LOG VALUES (null, ?, ?)
                        """,
                        [datetime.datetime.now(), message])
        msg_id = cursor.lastrowid
        cursor.close()
        if commit:
            self.conn.commit()
        
        return msg_id
    
    def delete_log(self, commit=True):
        """
        This function deletes all records in the Log table.
        
        Example::
        
            ds.delete_log()
            
        :param commit: do a database commit after the Resource is saved.
        """
        
        cursor = self.conn.cursor()
        cursor.execute("""
                        DELETE LOG
                        """,
                        [])
        cursor.close()
        if commit:
            self.conn.commit()
    
    def close(self):
        """
        Close the connection. Any outstanding commits are performed.
        
        Example::
        
            ds.close()
        """    
        self.conn.close()

    def delete_database(self):
        """
        This function deletes the current database file. The node environment
        does not use this method.
        
        Example::
        
            ds.delete_database()
        """
        
        self.conn.close()
        if os.path.isfile(self.filename):
            os.remove(self.filename)

    def _create_database(self):
        """
        This internal function creates the DataStore schema in the database. It 
        defines each table and their associated triggers. The method is called
        by __init__() if the database specified doesn't exist.
        
        There are two tables:
        
            'resource', with columns 'id' and 'object'. the object is the
            serialised and compressed resource.
            
            'resourcemetadata', with columns 'resource_id', 'key' and 'value'.
            The 'resource_id' links to the 'id' in the 'resource' table. 'key'
            contains the metadata full uri key and 'value' contains the
            metadata value. If it's a uri, then the full uri is stored to
            ensure that a correct match is made during a find method call.
            Suitable 'key' and 'value' values are defined by the 
            convert_key_value_pair_to_str method.
        """
        
        cursor = self.conn.cursor()
            
        # Create KEY VALUE table 
        cursor.execute("""
                        CREATE TABLE RESOURCE (
                          ID INTEGER NOT NULL PRIMARY KEY,
                          OBJECT BLOB
                        )""")       

        # Create delete trigger for RESOURCE table to ensure that if a resource
        # is deleted, then all of its metadata is also deleted.
        cursor.execute("""
                        CREATE TRIGGER FK_RESOURCE_ID_DELETE_TRIGGER
                        BEFORE DELETE ON RESOURCE
                        FOR EACH ROW BEGIN
                            DELETE from RESOURCEMETADATA 
                                WHERE RESOURCE_ID = OLD.ID;
                        END;
                        """) 
        
        cursor.execute("""
                        CREATE TABLE RESOURCEMETADATA (
                          RESOURCE_ID INTEGER NOT NULL CONSTRAINT FK_RESOURCE_ID 
                              REFERENCES RESOURCE(ID) ON DELETE CASCADE,
                          KEY TEXT,
                          VALUE TEXT
                        )""")     
        
        cursor.execute("""
                        CREATE TABLE LOG (
                          ID INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,
                          DATE_CREATED TEXT,
                          VALUE TEXT
                        )""")
        
# This index seems to reduce performance (at least for database_test.py)
#        # create an index for RESOURCEMETADATA
#        cursor.execute("""
#                        CREATE INDEX RESOURCEMETADATA_INDEX 
#                            ON RESOURCEMETADATA(KEY, VALUE);
#                        """)

        # Create insert trigger for RESOURCEMETADATA table
        cursor.execute("""
                        CREATE TRIGGER FK_RESOURCE_ID_INSERT_TRIGGER
                        BEFORE INSERT ON RESOURCEMETADATA
                        FOR EACH ROW BEGIN
                            SELECT RAISE(ROLLBACK, 
                                'INSERT ON TABLE "RESOURCEMETADATA" 
                                VIOLATES FOREIGN KEY CONSTRAINT 
                                "FK_RESOURCE_ID"')
                            WHERE  NEW.RESOURCE_ID IS NOT NULL
                                    AND (SELECT ID FROM RESOURCE 
                                        WHERE id = NEW.RESOURCE_ID) IS NULL;
                        END;
                        """)

        # Create update trigger for RESOURCEMETADATA table
        cursor.execute("""
                        CREATE TRIGGER FK_RESOURCE_ID_UPDATE_TRIGGER
                        BEFORE UPDATE ON RESOURCEMETADATA
                        FOR EACH ROW BEGIN
                            SELECT RAISE(ROLLBACK, 
                                'UPDATE ON TABLE "RESOURCEMETADATA" 
                                VIOLATES FOREIGN KEY CONSTRAINT 
                                "FK_RESOURCE_ID"')
                            WHERE  NEW.RESOURCE_ID IS NOT NULL
                                    AND (SELECT ID FROM RESOURCE 
                                        WHERE id = NEW.RESOURCE_ID) IS NULL;
                        END;
                        """)         
        cursor.close()
        self.conn.commit()

    def convert_key_value_pair_to_str(self, key_value):
        """
        This function converts the argument tuple 'key_value' into a
        key/value pair and returns.
        
        All Uri's return the full uri.

        :param key_value: tuple(key, value)
        :rtype: key/value pair.
        """

        (key, value) = key_value
        if key is None:
            key_str = None
        else:
            if type(key) != type(Uri()):
                raise TypeError("Key (%s) must be a Uri." % str(key))
            key_str = str(key.full())
        if type(value) == type(Uri()):
            value_str = str(value.full())
        elif value is None:
            value_str = None
        else:
            value_str = str(value)

        return (key_str, value_str)

    def get_datastore_pathname(self):
        return self.filename

class NotFoundException(Exception):
    """
    This class represents an exception for having not found an expected record
    in the DataStore.
    """ 
    pass
